package me.zengshuai;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
/***
 *  oracle数据库， 表结构查询 ，字段信息查询，字段注释查询 
 *  表字段查询 all_tab_columns
 *  表字段注释查询 all_col_comments
 *  @author 王华荣
 * 
 */
public class ReadOracleTableStructure {
    private static final String DRIVER_CLASS = "oracle.jdbc.driver.OracleDriver";
    private static final String DATABASE_URL = "jdbc:oracle:thin:@10.143.132.104:1521:newtest";
    private static final String DATABASE_USER = "test";
    private static final String DATABASE_PASSWORD = "test";
    private static Connection con = null;
     
    public static Connection getConnection() {
        try {
            Class.forName(DRIVER_CLASS);
            con=DriverManager.getConnection(DATABASE_URL,DATABASE_USER,DATABASE_PASSWORD);
            return con;
        } catch (Exception ex) {
            System.out.println("2:"+ex.getMessage());
        }
        return con;
    }
     
    /***
     * 
     * @throws SQLException
     */
    public static  List<HashMap<String,String>> sysoutStrTablePdmCloumns(String Table,String Owner) throws SQLException{
       getConnection();
         
        List<HashMap<String,String>> columns = new ArrayList<HashMap<String,String>>();
         
        try{
            Statement stmt = con.createStatement();
             
            String sql=
             "select "+
             "         comments as \"Name\","+
             "         a.column_name \"Code\","+
             "         a.DATA_TYPE as \"DataType\","+
             "         a.DATA_LENGTH as \"DataLength\","+
             "         b.comments as \"Comment\","+
             "         decode(c.column_name,null,'FALSE','TRUE') as \"Primary\","+
             "         decode(a.NULLABLE,'N','TRUE','Y','FALSE','') as \"Mandatory\","+
             "         '' \"sequence\""+
             "   from "+
             "       all_tab_columns a, "+
             "       all_col_comments b,"+
             "       ("+
             "        select a.constraint_name, a.column_name"+
             "          from user_cons_columns a, user_constraints b"+
             "         where a.constraint_name = b.constraint_name"+
             "               and b.constraint_type = 'P'"+
             "               and a.table_name = '"+Table+"'"+
             "       ) c"+
             "   where "+
             "     a.Table_Name=b.table_Name "+
             "     and a.column_name=b.column_name"+
             "     and a.Table_Name='"+Table+"'"+
             "     and a.owner=b.owner "+
             "     and a.owner='"+Owner+"'"+
             "     and a.COLUMN_NAME = c.column_name(+)" +
             "  order by a.COLUMN_ID";
//            System.out.println(sql);
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()){
                HashMap<String,String> map = new HashMap<String,String>();
                map.put("Name", rs.getString("Name"));
                map.put("Code", rs.getString("Code"));
                map.put("DataType", rs.getString("DataType"));
                map.put("DataLength", rs.getString("DataLength"));
                map.put("Comment", rs.getString("Comment"));
                map.put("Primary", rs.getString("Primary"));
                map.put("Mandatory", rs.getString("Mandatory"));
                columns.add(map);
//                System.out.println(map.get("Code")+":"+map.get("DataType"));
            }
            
        }
        catch (SQLException e){
            e.printStackTrace();
        }finally{
            con.close();
        }
        return columns;
    }
    
    /***
     * 获取oracle数据字段信息(字段名，字段类型，字段长度，数字精度，是否为空)
     * @param con
     * @param table  表名
     * @return   
     */
    public static Map<String,List<String>> getOracleColumn_info(Connection con,String table){
     String sql="select COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,DATA_SCALE,NULLABLE　from user_tab_columns where table_name =UPPER('"+table+"')";
     Statement st = null;
     Map<String,List<String>> map = new LinkedHashMap<String, List<String>>();
     try{
      st = con.createStatement();
      ResultSet rs = st.executeQuery(sql);
      List<String> val = null;
      while(rs.next()){
       val = new ArrayList<String>();
       String key = rs.getString("COLUMN_NAME");  //获取字段名
       val.add(rs.getString("DATA_TYPE"));  //获取数据类型
       val.add(rs.getString("DATA_LENGTH"));  //获取数据长度
       val.add(rs.getString("DATA_PRECISION"));  //获取数据长度
       val.add(rs.getString("DATA_SCALE"));  //获取数据精度
       val.add(rs.getString("NULLABLE"));  //获取是否为空
       map.put(key, val);
      }
     }catch (Exception e) {
      e.printStackTrace();
      // TODO: handle exception
     }
     return map;
    }
    public static void main(String[] args) throws SQLException{
         
//    	List<HashMap<String, String>> columns =   sysoutStrTablePdmCloumns("CUSTOMER_TEST1","TEST");
//        for(HashMap<String,String> column:columns){
//        	System.out.println(column);
//        	System.out.println(column.get("Code")+"   :   "+column.get("DataType"));
//        }
    	
    	Map<String,List<String>> columns = getOracleColumn_info(getConnection(),"CUSTOMER_TEST1");
    	
          	System.out.println(columns);
//          	System.out.println(column.get("Code")+"   :   "+column.get("DataType"));
      
    }
    
     
}
 
 